# import openpyxl
# import pymysql
#
#
# # workbook = openpyxl.load_workbook('user.xlsx')
# # worksheet = workbook['one sheet']
# # #
# # # data=[
# # #     ['序号','工牌号','花名','姓名','密码','电话'],
# # #     [1,375577514398,'ming89','杨晨',879909,1321640]
# # # ]
# # for i in worksheet.iter_rows():
# #     for j in i:
# #        print(j.value,end="\t")
# #     print()
#
# class my:
#     def __init__(self, host, database, user, password, port=3308, charset='utf8'):
#         self.host=host
#         self.port=port
#         self.database=database
#         self.user=user
#         self.password=password
#         self.charset=charset
#
#     def mysql(self,sql,data):
#         db = pymysql.connect(
#                     host=self.host,
#                     port=self.port,
#                     database=self.database,
#                     user=self.user,
#                     password=self.password,
#                     charset=self.charset
#                 )
#         try:
#             cursor=db.cursor()
#             cursor.execute(sql,data)
#             db.commit()
#             print('成功')
#         except Exception as e:
#             db.rollback()
#             print('失败',e)
#         finally:
#             db.close()
# #
# #
# #     # def open(self,a):
# #     #     workbook=openpyxl.load_workbook('user.xlsx')
# #     #     worksheet=workbook['one sheet']
# #     #     for i in worksheet.iter_rows(values_only=True):
# #     #     return
# #
# # # if __name__ == '__main__':
# # #     sql = 'INSERT INTO user_info VALUES (%s, %s, %s, %s, %s, %s)'
# # #
# # #     db = my('localhost', 'liu', 'root', '123456')
# # #
# # #     # 按行读取 Excel 数据
# # #     workbook = openpyxl.load_workbook('user.xlsx')
# # #     worksheet = workbook['one sheet']
# # #     data_rows = []
# # #     for row in worksheet.iter_rows(values_only=True):
# # #         data_rows.append(row)
# # #
# # #     # 跳过标题行（如果有）
# # #     if data_rows:
# # #         data_rows = data_rows[1:]  # 假设第一行为标题
# # #
# # #     # 执行批量插入
# # #     try:
# # #         for row in data_rows:
# # #             db.mysql(sql, row)
# # #     except Exception as e:
# # #         print("插入失败:", e)
# #
# if __name__=='__main__':
#     sql='insert into user_info1 values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
#     db=my('localhost','liu','root','123456')
#     workbook=openpyxl.load_workbook('ch.xlsx')
#     worksheet=workbook['one']
#     for row in worksheet.iter_rows(values_only=True):
#         if row and row[0] != '序号':
#             db.mysql(sql,row)
#
import openpyxl
import pymysql


class MySQLHandler:
    def __init__(self, host, database, user, password, port=3308, charset='utf8'):
        self.host = host
        self.port = port
        self.database = database
        self.user = user
        self.password = password
        self.charset = charset
        self.connection = None
        self.cursor = None

    def connect(self):
        """创建并返回数据库连接"""
        self.connection = pymysql.connect(
            host=self.host,
            port=self.port,
            database=self.database,
            user=self.user,
            password=self.password,
            charset=self.charset
        )
        self.cursor = self.connection.cursor()
        return self.connection

    def execute_query(self, sql, data=None):
        """执行SQL查询"""
        try:
            self.cursor.execute(sql, data)
            self.connection.commit()
            return True
        except Exception as e:
            self.connection.rollback()
            print(f'执行失败: {e}')
            return False

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()


if __name__ == '__main__':
    # SQL语句 - 确保列数匹配
    sql = 'INSERT INTO user_info2 VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

    # 创建数据库处理器
    db = MySQLHandler('localhost', 'liu', 'root', '123456')

    try:
        # 建立连接（只执行一次）
        db.connect()

        # 加载Excel文件
        workbook = openpyxl.load_workbook('ch.xlsx')

        # 检查工作表是否存在
        if 'one' not in workbook.sheetnames:
            raise ValueError("工作表 'one' 不存在")

        worksheet = workbook['one']

        # 处理数据行
        success_count = 0
        for row in worksheet.iter_rows(values_only=True):
            # 跳过空行和标题行
            if not row or row[0] == '序号':
                continue

            # 执行插入（复用同一个连接）
            if db.execute_query(sql, row):
                success_count += 1

        print(f"成功插入 {success_count} 条记录")

    except Exception as e:
        print(f"程序出错: {e}")
    finally:
        # 确保关闭连接
        db.close()
        workbook.close()  # 关闭Excel文件